package com.hh.bss.auth.util;

import com.hh.bss.auth.entity.SystemUser;
import com.hh.bss.common.ConstantDic;
import com.hh.bss.common.cache.SystemUserCache;

public class AuthSqlUtil {
	/**
	 * 客户 受保护信息
	 * 
	 * @param username
	 * @param custIdColumn
	 * @param creatorColumn
	 * @return
	 */
	public static String authCustomerProtectSQL(String username, String custIdColumn, String creatorColumn) {

		//

		// admin不受控制
		if (ConstantDic.ADMIN_USER.equals(username))
			return "";

		SystemUser systemUser = SystemUserCache.getSystemUserByUsername(username);

		StringBuffer sb = new StringBuffer();

		// GM 看全部
		if (SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_GM))
			return "";

		// 客户的职位相关人员（负责人，顾问）
		sb.append(" and (");
		sb.append(creatorColumn).append("='").append(username).append("'");
		sb.append(" or exists ( select 1 from contract_job t1,contract_job_partner t2 where  t1.cust_id=" + custIdColumn + " and t2.role in('" + ConstantDic.JOB_PARTNER_TYPE_MC + "','" + ConstantDic.JOB_PARTNER_TYPE_PC + "') and t1.id=t2.job_id and t2.account= '").append(username).append("' ) ");
		// 自己下级创建的
		// (20130318)sb.append(" or ").append(creatorColumn).append(" in ( select b3.account from system_user b3 where title_id in(select b1.id from system_branch_title b1 where b1.parent_ids like concat((select bb2.parent_ids from system_user b2,system_branch_title bb2 where bb2.id=b2.title_id and b2.account='").append(username).append("'),'%') and b1.id<>(select bb3.title_id from system_user bb3 where bb3.account='").append(username).append("')) )");
		sb.append(" or ").append(creatorColumn).append(" in ( ").append(getStaffSql(username)).append(" )");
		// 下级看那得到的
		// select b3.account from system_user b3 where title_id in(select b1.id
		// from system_branch_title b1 where b1.id in(select b2.title_id from
		// system_user b2 where b2.account='").append(username).append("'))
		// (20130318)sb.append(" or exists ( select 1 from contract_job t1,contract_job_partner t2 where  t1.cust_id="+custIdColumn+" and t2.role in('"+ConstantDic.JOB_PARTNER_TYPE_MC+"','"+ConstantDic.JOB_PARTNER_TYPE_PC+"') and t1.id=t2.job_id and t2.account in (").append(" select b3.account from system_user b3 where title_id in(select b1.id from system_branch_title b1 where b1.parent_ids like concat((select bb2.parent_ids from system_user b2,system_branch_title bb2 where bb2.id=b2.title_id and b2.account='").append(username).append("'),'%') and b1.id<>(select bb3.title_id from system_user bb3 where bb3.account='").append(username).append("')) ").append(" ) ) ");
		sb.append(" or exists ( select 1 from contract_job t1,contract_job_partner t2 where  t1.cust_id=" + custIdColumn + " and t2.role in('" + ConstantDic.JOB_PARTNER_TYPE_MC + "','" + ConstantDic.JOB_PARTNER_TYPE_PC + "') and t1.id=t2.job_id and t2.account in (").append(getStaffSql(username)).append(" ) ) ");
		// sb.append(" or exists ( select 1 from contract_job t1,contract_job_partner t2 where  t2.cust_id=t.custId and t2.role in('"+ConstantDic.JOB_PARTNER_TYPE_MC+"','"+ConstantDic.JOB_PARTNER_TYPE_PC+"') and t1.id=t2.job_id and t2.account in (").append(" select b3.account from system_user b3 where title_id in(select b1.id from system_branch_title b1 where b1.parent_ids like concat((select bb2.parent_ids from system_user b2,system_branch_title bb2 where bb2.id=b2.title_id and b2.account='").append(username).append("'),'%') and b1.id<>(select bb3.title_id from system_user bb3 where bb3.account='").append(username).append("')) ").append(" ) ) ");

		sb.append(" )");

		return sb.toString();
	}

	public static String authReportSQL(String username, String creatorColumn) {
		// admin不受控制
		if (ConstantDic.ADMIN_USER.equals(username))
			return "";

		SystemUser systemUser = SystemUserCache.getSystemUserByUsername(username);

		StringBuffer sb = new StringBuffer();

		// GM 看全部
		if (SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_GM))
			return "";
		// A1看全部
		if (SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_A1))
			return "";

		// 客户的职位相关人员（负责人，顾问）
		sb.append(" and (");
		sb.append(creatorColumn).append("='").append(username).append("'");

		// 自己下级创建的
		// (20130318)sb.append(" or ").append(creatorColumn).append(" in ( select b3.account from system_user b3 where title_id in(select b1.id from system_branch_title b1 where b1.parent_ids like concat((select bb2.parent_ids from system_user b2,system_branch_title bb2 where bb2.id=b2.title_id and b2.account='").append(username).append("'),'%') and b1.id<>(select bb3.title_id from system_user bb3 where bb3.account='").append(username).append("')) )");
		sb.append(" or ").append(creatorColumn).append(" in ( ").append(getStaffSql(username)).append(" )");
		// 自己同直属部门的
		// sb.append(" or ").append(creatorColumn).append(" in ( ").append(getSameDeptStaffSql(username)).append(" )");
		sb.append(" )");

		return sb.toString();
	}

	public static String authBAReportSQL(String username, String creatorColumn) {
		// admin不受控制
		if (ConstantDic.ADMIN_USER.equals(username))
			return "";

		SystemUser systemUser = SystemUserCache.getSystemUserByUsername(username);

		StringBuffer sb = new StringBuffer();

		// GM 看全部
		if (SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_GM))
			return "";
		// A1看全部
		if (SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_A1))
			return "";

		// 客户的职位相关人员（负责人，顾问）
		sb.append(" and (");
		sb.append(creatorColumn).append("='").append(username).append("'");
		// 自己同直属部门的
		// (20130318)sb.append(" or ").append(creatorColumn).append(" in ( select b3.account from system_user b3 where dept_id in(select b2.dept_id from system_user b2 where b2.account='").append(username).append("')  )");
		sb.append(" or ").append(creatorColumn).append(" in ( ").append(getSameDeptStaffSql(username)).append(" )");
		// 自己下级创建的
		// (20130318)sb.append(" or ").append(creatorColumn).append(" in ( select b3.account from system_user b3 where title_id in(select b1.id from system_branch_title b1 where b1.parent_ids like concat((select bb2.parent_ids from system_user b2,system_branch_title bb2 where bb2.id=b2.title_id and b2.account='").append(username).append("'),'%') and b1.id<>(select bb3.title_id from system_user bb3 where bb3.account='").append(username).append("')) )");
		sb.append(" or ").append(creatorColumn).append(" in ( ").append(getStaffSql(username)).append(" )");

		sb.append(" )");

		return sb.toString();
	}
	
	public static String authConfSQL(String username, String creatorColumn) {
		// admin不受控制
		if (ConstantDic.ADMIN_USER.equals(username))
			return "";

		SystemUser systemUser = SystemUserCache.getSystemUserByUsername(username);

		StringBuffer sb = new StringBuffer();

		// GM 看全部
		if (SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_GM))
			return "";
		

		// 客户的职位相关人员（负责人，顾问）
		sb.append(" and (");
		sb.append(creatorColumn).append("='").append(username).append("'");

		
		if (SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_Branch_Manager)) {

			
			sb.append(" or ").append(creatorColumn).append(" in ( ").append(getSameBranchCompanyStaffSql(username)).append(" )");
			
			
		}		
        if (SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_Manager)) {

    	   sb.append(" or ").append(creatorColumn).append(" in ( ").append(getSameDeptStaffSql(username)).append(" )");
		   //sb.append(" or ").append(creatorColumn).append(" in ( ").append(getSameBranchCompanyStaffSql(username)).append(" )");
			
			
		}		
		
		// 自己同直属部门的
		// (20130318)sb.append(" or ").append(creatorColumn).append(" in ( select b3.account from system_user b3 where dept_id in(select b2.dept_id from system_user b2 where b2.account='").append(username).append("')  )");
		
		// 自己下级创建的
		// (20130318)sb.append(" or ").append(creatorColumn).append(" in ( select b3.account from system_user b3 where title_id in(select b1.id from system_branch_title b1 where b1.parent_ids like concat((select bb2.parent_ids from system_user b2,system_branch_title bb2 where bb2.id=b2.title_id and b2.account='").append(username).append("'),'%') and b1.id<>(select bb3.title_id from system_user bb3 where bb3.account='").append(username).append("')) )");
		sb.append(" or ").append(creatorColumn).append(" in ( ").append(getStaffSql(username)).append(" )");

		sb.append(" )");

		return sb.toString();
	}

	public static String getStaffSql(String username) {
		// StringBuffer sql=new StringBuffer();
		// sql.append(" select b1.account from system_user b1,system_user_title b2,system_branch_title b3, ");
		// sql.append(" (select c.parent_ids,a.title_id titleId from  system_user_title a,system_user b,system_branch_title c where a.title_id=c.id and a.user_id=b.id and b.account='").append(username).append("') b4 ");
		// sql.append(" where b1.id=b2.user_id and b2.title_id=b3.id and b2.title_id<>b4.titleId ");
		// sql.append(" and b3.parent_ids like concat(b4.parent_ids,'%') ");
		//
		//
		// return sql.toString();
		return SystemUserCache.getUsernameStaff(username);
	}

	public static String getSameDeptStaffSql(String username) {
		return SystemUserCache.getUsernameSameDeptStaff(username);
	}

	public static String getSameBranchCompanyStaffSql(String username) {
		// StringBuffer sql=new StringBuffer();
		// sql.append(" select b1.account from system_user b1,system_user_title b2,system_branch_title b3,system_branch_dept b4 ");
		// sql.append(" where b1.id=b2.user_id and b2.title_id=b3.id and b3.dept_id=b4.id  and  b4.branch_company_id in (select a4.branch_company_id from system_user a1,system_user_title a2,system_branch_title a3,system_branch_dept a4 where a1.id=a2.user_id and a2.title_id=a3.id and a3.dept_id=a4.id  and a1.account='").append(username).append("' ) ");
		//
		// return sql.toString();
		return SystemUserCache.getUsernameSameCompanyStaff(username);
	}

	/**
	 * 客户敏感保密信息，一般是银行账号
	 * 
	 * @param username
	 * @param custIdColumn
	 * @param creatorColumn
	 * @return
	 */
	public static String authCustomerSecSQL(String username, String custIdColumn, String creatorColumn) {

		//

		// admin不受控制
		if (ConstantDic.ADMIN_USER.equals(username))
			return "";

		SystemUser systemUser = SystemUserCache.getSystemUserByUsername(username);

		StringBuffer sb = new StringBuffer();

		// GM ，Account看全部
		if (SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_ACCOUNT) || SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_GM) || SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_SUPER_USER))
			return "";

		// CONTRACTOR看全部
		if (SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_CONTRACTOR))
			return "";

		// 客户的职位相关人员（负责人）
		sb.append(" and (");
		sb.append(creatorColumn).append("='").append(username).append("'");
		sb.append(" or exists ( select 1 from contract_job t1,contract_job_partner t2 where  t1.cust_id=" + custIdColumn + " and t2.role in('" + ConstantDic.JOB_PARTNER_TYPE_MC + "','" + ConstantDic.JOB_PARTNER_TYPE_PC + "') and t1.id=t2.job_id and t2.account= '").append(username).append("' ) ");
		// 自己下级创建的
		// 20130318
		// sb.append(" or ").append(creatorColumn).append(" in ( select b3.account from system_user b3 where title_id in(select b1.id from system_branch_title b1 where b1.parent_ids like concat((select bb2.parent_ids from system_user b2,system_branch_title bb2 where bb2.id=b2.title_id and b2.account='").append(username).append("'),'%') and b1.id<>(select bb3.title_id from system_user bb3 where bb3.account='").append(username).append("')) )");
		sb.append(" or ").append(creatorColumn).append(" in ( ").append(getStaffSql(username)).append(" )");
		// 下级看那得到的
		// select b3.account from system_user b3 where title_id in(select b1.id
		// from system_branch_title b1 where b1.id in(select b2.title_id from
		// system_user b2 where b2.account='").append(username).append("'))
		// 20130318sb.append(" or exists ( select 1 from contract_job t1,contract_job_partner t2 where  t1.cust_id="+custIdColumn+" and t2.role in('"+ConstantDic.JOB_PARTNER_TYPE_MC+"','"+ConstantDic.JOB_PARTNER_TYPE_PC+"') and t1.id=t2.job_id and t2.account in (").append(" select b3.account from system_user b3 where title_id in(select b1.id from system_branch_title b1 where b1.parent_ids like concat((select bb2.parent_ids from system_user b2,system_branch_title bb2 where bb2.id=b2.title_id and b2.account='").append(username).append("'),'%') and b1.id<>(select bb3.title_id from system_user bb3 where bb3.account='").append(username).append("')) ").append(" ) ) ");
		sb.append(" or exists ( select 1 from contract_job t1,contract_job_partner t2 where  t1.cust_id=" + custIdColumn + " and t2.role in('" + ConstantDic.JOB_PARTNER_TYPE_MC + "','" + ConstantDic.JOB_PARTNER_TYPE_PC + "') and t1.id=t2.job_id and t2.account in ( ").append(getStaffSql(username)).append(" ").append(" ) ) ");

		// sb.append(" or exists ( select 1 from contract_job t1,contract_job_partner t2 where  t2.cust_id=t.custId and t2.role in('"+ConstantDic.JOB_PARTNER_TYPE_MC+"','"+ConstantDic.JOB_PARTNER_TYPE_PC+"') and t1.id=t2.job_id and t2.account in (").append(" select b3.account from system_user b3 where title_id in(select b1.id from system_branch_title b1 where b1.parent_ids like concat((select bb2.parent_ids from system_user b2,system_branch_title bb2 where bb2.id=b2.title_id and b2.account='").append(username).append("'),'%') and b1.id<>(select bb3.title_id from system_user bb3 where bb3.account='").append(username).append("')) ").append(" ) ) ");

		sb.append(" )");

		return sb.toString();
	}

	public static String authBDCustomerSQL(String username, String custIdColumn, String creatorColumn) {
		if (ConstantDic.ADMIN_USER.equals(username))
			return "";

		SystemUser systemUser = SystemUserCache.getSystemUserByUsername(username);

		StringBuffer sb = new StringBuffer();

		// Account 角色 看全部
		// GM 看全部
		if (SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_SC_Manager) || SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_SC_Supervisor) || SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_GM) || SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_ACCOUNT) || SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_SUPER_USER))
			return "";

		// 客户职位相关人 看属于自己的职位客户的
		sb.append(" and (");
		sb.append(creatorColumn).append("='").append(username).append("'");
		sb.append(" or exists ( select 1 from contract_job t1,contract_job_partner t2 where  t1.cust_id=" + custIdColumn + " and t1.id=t2.job_id and t2.account= '").append(username).append("' ) ");
		sb.append(" or exists ( select 1 from customer_contract t1,customer_contract_staff t2 where  t1.cust_id=").append(custIdColumn).append(" and t1.id=t2.contract_id and  t2.account='").append(username).append("' ) ");
		// 下级创建的
		// 20130318sb.append(" or ").append(creatorColumn).append(" in ( select b3.account from system_user b3 where title_id in(select b1.id from system_branch_title b1 where b1.parent_ids like concat((select bb2.parent_ids from system_user b2,system_branch_title bb2 where bb2.id=b2.title_id and b2.account='").append(username).append("'),'%') and b1.id<>(select bb3.title_id from system_user bb3 where bb3.account='").append(username).append("')) )");
		sb.append(" or ").append(creatorColumn).append(" in ( ").append(getStaffSql(username)).append(" )");
		// 下级是合同客户经理的
		// 20130318sb.append(" or exists ( select 1 from customer_contract t1,customer_contract_staff t2 where  t1.cust_id=").append(custIdColumn).append(" and t1.id=t2.contract_id and t2.role='").append(ConstantDic.ContractStaffRole_BDM).append("'   and t2.account in (").append(" select b3.account from system_user b3 where title_id in(select b1.id from system_branch_title b1 where b1.parent_ids like concat((select bb2.parent_ids from system_user b2,system_branch_title bb2 where bb2.id=b2.title_id and b2.account='").append(username).append("'),'%') and b1.id<>(select bb3.title_id from system_user bb3 where bb3.account='").append(username).append("')) ").append(" ) ) ");
		sb.append(" or exists ( select 1 from customer_contract t1,customer_contract_staff t2 where  t1.cust_id=").append(custIdColumn).append(" and t1.id=t2.contract_id and t2.role='").append(ConstantDic.ContractStaffRole_BDM).append("'   and t2.account in ( ").append(getStaffSql(username)).append(" ").append(" ) ) ");
		// 下级可以看到的
		// select b3.account from system_user b3 where title_id in(select b1.id
		// from system_branch_title b1 where b1.id in(select b2.title_id from
		// system_user b2 where b2.account='").append(username).append("'))
		// 20130318sb.append(" or exists ( select 1 from contract_job t1,contract_job_partner t2 where  t1.cust_id="+custIdColumn+" and t1.id=t2.job_id and t2.account in (").append(" select b3.account from system_user b3 where title_id in(select b1.id from system_branch_title b1 where b1.parent_ids like concat((select bb2.parent_ids from system_user b2,system_branch_title bb2 where bb2.id=b2.title_id and b2.account='").append(username).append("'),'%') and b1.id<>(select bb3.title_id from system_user bb3 where bb3.account='").append(username).append("')) ").append(" ) ) ");
		sb.append(" or exists ( select 1 from contract_job t1,contract_job_partner t2 where  t1.cust_id=" + custIdColumn + " and t1.id=t2.job_id and t2.account in ( ").append(getStaffSql(username)).append(" ").append(" ) ) ");

		sb.append(" )");

		return sb.toString();
	}

	public static String authReportCustomerSQL(String username, String custIdColumn, String creatorColumn) {
		if (ConstantDic.ADMIN_USER.equals(username))
			return "";

		SystemUser systemUser = SystemUserCache.getSystemUserByUsername(username);

		StringBuffer sb = new StringBuffer();

		// Account 角色 看全部
		// GM 看全部
		if (SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_SC_Manager) || SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_SC_Supervisor) || SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_GM) || SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_ACCOUNT) || SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_SUPER_USER))
			return "";

		// CONTRACTOR看全部
		if (SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_CONTRACTOR))
			return "";

		// 客户职位相关人 看属于自己的职位客户的
		sb.append(" and (");
		// sb.append(creatorColumn).append("='").append(username).append("'");
		sb.append("  exists ( select 1 from contract_job t1,contract_job_partner t2 where  t1.cust_id=" + custIdColumn + " and t1.id=t2.job_id and t2.account= '").append(username).append("' ) ");
		sb.append(" or exists ( select 1 from customer_contract t1,customer_contract_staff t2 where  t1.cust_id=").append(custIdColumn).append(" and t1.id=t2.contract_id and  t2.account='").append(username).append("' ) ");
		sb.append(" or exists ( select 1 from customer_contract t1,customer_contract_service_company b,customer_contract_staff t2 where t1.id=b.contract_id  and  b.cust_id=").append(custIdColumn).append(" and t1.id=t2.contract_id and  t2.account='").append(username).append("' ) ");
		// 下级创建的
		// sb.append(" or ").append(creatorColumn).append(" in ( select b3.account from system_user b3 where title_id in(select b1.id from system_branch_title b1 where b1.parent_ids like concat((select bb2.parent_ids from system_user b2,system_branch_title bb2 where bb2.id=b2.title_id and b2.account='").append(username).append("'),'%') and b1.id<>(select bb3.title_id from system_user bb3 where bb3.account='").append(username).append("')) )");

		// 下级是合同相关的
		// 20130318sb.append(" or exists ( select 1 from customer_contract t1,customer_contract_staff t2 where  t1.cust_id=").append(custIdColumn).append(" and t1.id=t2.contract_id    and t2.account in (").append(" select b3.account from system_user b3 where title_id in(select b1.id from system_branch_title b1 where b1.parent_ids like concat((select bb2.parent_ids from system_user b2,system_branch_title bb2 where bb2.id=b2.title_id and b2.account='").append(username).append("'),'%') and b1.id<>(select bb3.title_id from system_user bb3 where bb3.account='").append(username).append("')) ").append(" ) ) ");
		sb.append(" or exists ( select 1 from customer_contract t1,customer_contract_staff t2 where  t1.cust_id=").append(custIdColumn).append(" and t1.id=t2.contract_id    and t2.account in ( ").append(getStaffSql(username)).append(" ").append(" ) ) ");

		// 20130318sb.append(" or exists ( select 1 from customer_contract t1,customer_contract_service_company b,customer_contract_staff t2 where t1.id=b.contract_id  and  b.cust_id=").append(custIdColumn).append(" and t1.id=t2.contract_id    and t2.account in (").append(" select b3.account from system_user b3 where title_id in(select b1.id from system_branch_title b1 where b1.parent_ids like concat((select bb2.parent_ids from system_user b2,system_branch_title bb2 where bb2.id=b2.title_id and b2.account='").append(username).append("'),'%') and b1.id<>(select bb3.title_id from system_user bb3 where bb3.account='").append(username).append("')) ").append(" ) ) ");
		sb.append(" or exists ( select 1 from customer_contract t1,customer_contract_service_company b,customer_contract_staff t2 where t1.id=b.contract_id  and  b.cust_id=").append(custIdColumn).append(" and t1.id=t2.contract_id    and t2.account in ( ").append(getStaffSql(username)).append(" ").append(" ").append(" ) ) ");
		// 下级可以看到的
		// select b3.account from system_user b3 where title_id in(select b1.id
		// from system_branch_title b1 where b1.id in(select b2.title_id from
		// system_user b2 where b2.account='").append(username).append("'))
		// 20130318sb.append(" or exists ( select 1 from contract_job t1,contract_job_partner t2 where  t1.cust_id="+custIdColumn+" and t1.id=t2.job_id and t2.account in (").append(" select b3.account from system_user b3 where title_id in(select b1.id from system_branch_title b1 where b1.parent_ids like concat((select bb2.parent_ids from system_user b2,system_branch_title bb2 where bb2.id=b2.title_id and b2.account='").append(username).append("'),'%') and b1.id<>(select bb3.title_id from system_user bb3 where bb3.account='").append(username).append("')) ").append(" ) ) ");
		sb.append(" or exists ( select 1 from contract_job t1,contract_job_partner t2 where  t1.cust_id=" + custIdColumn + " and t1.id=t2.job_id and t2.account in (").append(getStaffSql(username)).append(" ) ) ");

		sb.append(" )");

		return sb.toString();
	}

	/**
	 * 职位权限控制
	 * 
	 * @param username
	 * @param custIdColumn
	 * @param creatorColumn
	 * @return
	 */

	public static String authJobSQL(String username, String jobIdColumn, String creatorColumn) {
		if (ConstantDic.ADMIN_USER.equals(username))
			return "";

		SystemUser systemUser = SystemUserCache.getSystemUserByUsername(username);

		StringBuffer sb = new StringBuffer();

		// Account 角色 看全部
		// GM,SC Manager, SC Supervisor 看全部
		if (SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_SC_Manager) || SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_SC_Supervisor) || SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_GM) || SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_ACCOUNT) || SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_SUPER_USER))
			return "";

		//
		if (SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_Specialist))
			return "";
		if (SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_S2))
			return "";

		// CONTRACTOR看全部
		if (SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_CONTRACTOR))
			return "";
		// 客户职位相关人 看属于自己的职位客户的
		sb.append(" and (");
		sb.append(creatorColumn).append("='").append(username).append("'");
		sb.append(" or exists ( select 1 from contract_job_partner t2 where  t2.job_id=" + jobIdColumn + "  and t2.account= '").append(username).append("' ) ");
		// 下级创建的
		// 20130318sb.append(" or ").append(creatorColumn).append(" in ( select b3.account from system_user b3 where title_id in(select b1.id from system_branch_title b1 where b1.parent_ids like concat((select bb2.parent_ids from system_user b2,system_branch_title bb2 where bb2.id=b2.title_id and b2.account='").append(username).append("'),'%') and b1.id<>(select bb3.title_id from system_user bb3 where bb3.account='").append(username).append("')) )");
		sb.append(" or ").append(creatorColumn).append(" in ( ").append(getStaffSql(username)).append(" )");
		// 下级可以看到的
		// select b3.account from system_user b3 where title_id in(select b1.id
		// from system_branch_title b1 where b1.id in(select b2.title_id from
		// system_user b2 where b2.account='").append(username).append("'))
		// 20130318sb.append(" or exists ( select 1 from contract_job_partner t2 where  t2.job_id="+jobIdColumn+"  and t2.account in (").append(" select b3.account from system_user b3 where title_id in(select b1.id from system_branch_title b1 where b1.parent_ids like concat((select bb2.parent_ids from system_user b2,system_branch_title bb2 where bb2.id=b2.title_id and b2.account='").append(username).append("'),'%') and b1.id<>(select bb3.title_id from system_user bb3 where bb3.account='").append(username).append("')) ").append(" ) ) ");
		sb.append(" or exists ( select 1 from contract_job_partner t2 where  t2.job_id=" + jobIdColumn + "  and t2.account in ( ").append(getStaffSql(username)).append(" ").append(" ) ) ");

		sb.append(" )");

		return sb.toString();
	}

	/**
	 * 职位保密信息，助理不能看
	 * 
	 * @param username
	 * @param jobIdColumn
	 * @param creatorColumn
	 * @return
	 */
	public static String authJobSecSQL(String username, String jobIdColumn, String creatorColumn) {
		if (ConstantDic.ADMIN_USER.equals(username))
			return "";

		SystemUser systemUser = SystemUserCache.getSystemUserByUsername(username);

		StringBuffer sb = new StringBuffer();

		// Account 角色 看全部
		// GM,SC Manager, SC Supervisor 看全部
		if (SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_SC_Manager) || SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_SC_Supervisor) || SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_GM) || SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_SUPER_USER))
			return "";

		// 助理不能看
		sb.append(" and (");

		sb.append(" not exists ( select 1 from contract_job_partner t2 where  t2.job_id=" + jobIdColumn + "  and t2.account= '").append(username).append("'  and t2.role in('" + ConstantDic.JOB_PARTNER_TYPE_BA + "') ) ");

		sb.append(" )");

		return sb.toString();
	}

	public static String authPnSQL(String username, String creatorColumn) {

		//

		// admin不受控制
		if (ConstantDic.ADMIN_USER.equals(username))
			return "";

		SystemUser systemUser = SystemUserCache.getSystemUserByUsername(username);

		StringBuffer sb = new StringBuffer();

		// Account 角色 看全部
		// GM 看全部
		if (SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_ACCOUNT) || SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_GM))
			return "";

		//
		// CONTRACTOR看全部
		if (SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_CONTRACTOR))
			return " ";
		// 合同--》客户--》职位--》职位下相关的人员 可以看到 客户相关的合同
		sb.append(" and (");
		sb.append(creatorColumn).append("='").append(username).append("'");

		sb.append(" or exists ( select 1 from contract_job t1,contract_job_partner t2 where  t1.cust_id=t.custId and t1.id=t2.job_id and t2.account= '").append(username).append("' ) ");
		// 自己下级创建的
		// 20130318sb.append(" or ").append(creatorColumn).append(" in ( select b3.account from system_user b3 where title_id in(select b1.id from system_branch_title b1 where b1.parent_ids like concat((select bb2.parent_ids from system_user b2,system_branch_title bb2 where bb2.id=b2.title_id and b2.account='").append(username).append("'),'%') and b1.id<>(select bb3.title_id from system_user bb3 where bb3.account='").append(username).append("')) )");
		sb.append(" or ").append(creatorColumn).append(" in ( ").append(getStaffSql(username)).append(" )");
		// 下级是职位合作人员
		// 20130318sb.append(" or exists ( select 1 from contract_job t1,contract_job_partner t2 where  t1.cust_id=t.custId and t1.id=t2.job_id and t2.account in (").append(" select b3.account from system_user b3 where title_id in(select b1.id from system_branch_title b1 where b1.parent_ids like concat((select bb2.parent_ids from system_user b2,system_branch_title bb2 where bb2.id=b2.title_id and b2.account='").append(username).append("'),'%') and b1.id<>(select bb3.title_id from system_user bb3 where bb3.account='").append(username).append("')) ").append(" ) ) ");
		sb.append(" or exists ( select 1 from contract_job t1,contract_job_partner t2 where  t1.cust_id=t.custId and t1.id=t2.job_id and t2.account in (  ").append(getStaffSql(username)).append("  ").append(" ) ) ");
		sb.append(" )");

		return sb.toString();
	}

	public static String authJobRecomGroup(String username, String creatorColumn) {

		// admin不受控制
		if (ConstantDic.ADMIN_USER.equals(username))
			return "";

		SystemUser systemUser = SystemUserCache.getSystemUserByUsername(username);

		StringBuffer sb = new StringBuffer();

		// Account 角色 看全部
		// GM 看全部
		// BM 全部
		if (SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_ACCOUNT) || SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_GM) || SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_Branch_Manager) || SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_SUPER_USER))
			return "";

		// Contractor看全部
		if (SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_CONTRACTOR))
			return "";
		// M or C R 可以看到自己组内的所有合同

		sb.append(" and (");
		sb.append(creatorColumn).append("='").append(username).append("'");
		if (SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_Manager) || SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_Consultant)) {
			sb.append(" or ").append(creatorColumn).append(" in ");
			sb.append(" ( ").append(AuthSqlUtil.getSameDeptStaffSql(username)).append(" ) ");
		}
		sb.append(" )");

		return sb.toString();
	}

}
